package com.jiao.comm.utils;

import cn.hutool.core.collection.CollectionUtil;
import com.jiao.comm.entity.QueryContent;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JDBCUtils {
	/**
	 * 关闭连接, 支持 PreparedStatement、ResultSet、Connection 关闭.
	 * @param 
	 * @return
	 */
	public static void close(Object... obj) throws SQLException{
		if(obj != null && obj.length > 0){
			for(Object item : obj){
				if(item instanceof PreparedStatement){
					PreparedStatement ps = (PreparedStatement) item;
					ps.close();
				
				}else if(item instanceof ResultSet){
					ResultSet rs = (ResultSet) item;
					rs.close();
				
				} else if(item instanceof Connection){
					Connection con = (Connection) item;
					if(!con.isClosed()){
						if(!con.getAutoCommit()) {con.commit();}
						con.close();
					}
				}
			}
		}
	}
	

	/**
     * 操作 更新、插入、删除.
	 * @param con
	 * @param sql
	 * @param params 参数列表
	 * @return
	 */
	public static int execuCUD(Connection con, String sql, Object... params) throws SQLException{
		showSql(sql, params);
		
		int count = 0;
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(sql);

			if(params != null && params.length > 0){
				for(int i = 0; i < params.length; i++){
					stmt.setObject(i+1, params[i]);
				}
			}

			count = stmt.executeUpdate();
		} finally {
			close(stmt);
		}
		return count;
	}

	/**
	 * <p>
	 * 		<h1>操作 更新、插入、删除</h1>
	 * 		示例(插入、更新操作  同理):<br/>

	 * </p>
	 * @param con
	 * @param sql
	 * @param params 参数列表
	 * @return
	 */
	public static int execuCUD(Connection con, String sql, Map<String, Object> map) throws SQLException{
		showSql(sql, map);
		int count = 0;
		
		List<Object> params = new ArrayList<Object>(map.size());
		sql = analysisPlaceholder(sql, map, params);
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(sql);

			if(params != null && params.size() > 0){
				for(int i = 0; i < params.size(); i++){
					stmt.setObject(i+1, params.get(i));
				}
			}

			count = stmt.executeUpdate();
		} finally {
			close(stmt);
		}
		return count;
	}
	
	/**
	 * 批量插入.
	 * <p>
	 * 		批量插入<br/>
	 * 		使用示例：<br/>
	 * 			String sql = " insert into person (name, age) values ( #{name}, #{age}) ";<br/>
	 * 			List<Person> list = new ArrayList<Person>();<br/>
	 * 			list.add(new Person("老王", 15));<br/>
	 * 			list.add(new Person("老李", 16));<br/>
	 * 
	 * 			insBatch(con, sql. list);		//成功批量插入2条
	 * </p>
	 * @param con
	 * @param sql
	 * @param params 支持实现迭代器
	 * @return
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	public static int[] insBatch(Connection con, String sql, Iterable params) throws SQLException, IllegalArgumentException, IllegalAccessException{
		showSql(sql, null);
		String sqlNew = sql.replaceAll("#\\{[\\w_-]{1,}\\}", " ? ");
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(sqlNew);

			//获取占位符中字段列表
			List<String> fieldParam = new ArrayList<String>();
			Pattern pattern = Pattern.compile("(#\\{[\\w_-]{1,}\\})");
			Matcher matcher = pattern.matcher(sql);
			String key = null;

			while (matcher.find()) {
				key = matcher.group(1);
				fieldParam.add(key.substring(2, key.length() - 1));
			}

			Class classObj = null;
			Iterator iterator = params.iterator();
			Object element = null;
			Map<String, Object> fieldAndValueMap = null;

			while(iterator.hasNext()){
				element = iterator.next();

				classObj = element.getClass();
				fieldAndValueMap = new HashMap<String, Object>(classObj.getFields().length);

				for(Field item : classObj.getFields()){
					fieldAndValueMap.put(item.getName(), item.get(element));
				}

				for(int j = 0; j < fieldParam.size(); j++){
					stmt.setObject(j+1, fieldAndValueMap.get(fieldParam.get(j)));
				}
				stmt.addBatch();
			}

			return stmt.executeBatch();
		} finally {
			close(stmt);
		}
	}

	/**
	 * 批量插入.
	 * <p>
	 * 		批量插入<br/>
	 * 		使用示例：<br/>
	 * 			String sql = " insert into person (name, age) values ( ?, ?) ";<br/>
	 * 			ist<List<Object>> list = new ArrayList<>();<br/>
	 * 			list.add(Arrays.asList("老李", 16));<br/>
	 * 			list.add(Arrays.asList("老李", 13));<br/>
	 *
	 * 			insBatch(con, sql. list);		//成功批量插入2条
	 * </p>
	 * @param con
	 * @param sql
	 * @param params 支持实现迭代器
	 * @return
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	public static int[] insBatch(Connection con, String sql, List<List<Object>> allData) throws SQLException, IllegalArgumentException, IllegalAccessException{
		if (CollectionUtil.isEmpty(allData)) {
			return null;
		}

		showSql(sql, null);
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(sql);

			for (int i = 0; i < allData.size(); i++) {
				List<Object> itemList = allData.get(i);

				for(int j = 0; j < itemList.size(); j++){
					stmt.setObject(j+1, itemList.get(j));
				}

				stmt.addBatch();
			}

			return stmt.executeBatch();

		} finally {
			close(stmt);
		}
	}

	/**
	 * 查询.
	 * @param 
	 * @return
	 * @throws SQLException 
	 */
	public static ResultSet select(Connection con, String sql, Object... params) throws SQLException{
		showSql(sql, params);

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.prepareStatement(sql);
			if(params != null && params.length > 0){
				for(int i = 0; i < params.length; i++){
					stmt.setObject(i+1, params[i]);
				}
			}

			rs = stmt.executeQuery();
		} catch (SQLException e) {
			throw e;
		}

		return rs;
	}

	/**
	 * 查询.
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public static <T> T select(Connection con, String sql, QueryContent<T> queryContent, Object... params) throws SQLException{

		showSql(sql, params);

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.prepareStatement(sql);
			if(params != null && params.length > 0){
				for(int i = 0; i < params.length; i++){
					stmt.setObject(i+1, params[i]);
				}
			}

			rs = stmt.executeQuery();

			T obj = queryContent.exectu(rs);


			return obj;
		} finally {
			close(rs, stmt);
		}
	}

	/**
	 * 查询, 以占位符注入参数.
	 * @param con
	 * @param sql
	 * @param queryContent
	 * @param map 
	 * @return
	 * @throws SQLException 
	 */
	public static <T> T select(Connection con, String sql, QueryContent<T> queryContent, Map<String, Object> map) throws SQLException{
		showSql(sql, map);
		
		List<Object> paramsList = new ArrayList<Object>(map.size());
		sql = analysisPlaceholder(sql, map, paramsList);

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.prepareStatement(sql);
			if(paramsList != null){
				for(int i = 0, size = paramsList.size(); i < size; i++){
					stmt.setObject(i + 1, paramsList.get(i));
				}
			}

			rs = stmt.executeQuery();

			T obj = queryContent.exectu(rs);


			return obj;
		} finally {
			close(rs, stmt);
		}
	}
	
	/**
	 * <p>解析占位符</p>
	 * @param sql
	 * @param list 内部会解析好jdbc需要是值, 并去填充集合, 在此之前会先去清空集合
	 * @return String 解析并替换完的值
	 */
	private static String analysisPlaceholder(String sql, Map<String, Object> map, List<Object> jdbcParam){
		if(sql == null || sql.length() == 0){
			return null;
		}
		
		if(jdbcParam == null || map == null){
			return sql;
		}
		
		//设置参数
		if(map != null && map.size() > 0){
			jdbcParam.clear();
			String key = null;
			
			//${xxx} 替换为对应值
			for(String item : map.keySet()){
				key = "\\{"+item+"\\}";
				
				if(sql.indexOf("${"+item+"}") > -1){
					sql = sql.replaceAll("\\$"+key, map.get(item).toString());
				}
			}
			
			//替换 #{xxx} 为 ?
			Pattern pattern = Pattern.compile("(#\\{[\\w_-]{1,}\\})");
			Matcher matcher = pattern.matcher(sql);  
			while (matcher.find()) {  
				key = matcher.group(1);
				sql = sql.replace(key, "?");
				jdbcParam.add(map.get(key.substring(2, key.length() - 1)));
			}  
		}
		
		return sql;
	}
	
	/**
	 * <p>打印sql</p>
	 * @param 
	 * @return
	 */
	private static void showSql(String sql, Object params){
		if(sql != null){
			StringBuilder paramsLog = new StringBuilder();
			if(params != null){
				if(params instanceof Object[]){
					Object[] list = (Object[]) params;
					for(Object item : list){
						paramsLog.append(item.toString()).append(" - ");
					}
				
				} else if (params instanceof Map){
					Map<String, Object> map = (Map<String, Object>) params;
					for(String item : map.keySet()){
						paramsLog.append(item).append(":").append(map.get(item)).append(" - ");
					}
				}
			}
			
			writeLog(sql);
			writeLog("params : " + paramsLog.toString());
		}
	}
	
	/**
	 * <p>写入日志</p>
	 * @param logMessage 日志内容
	 * @return
	 */
	private static void writeLog(String logMessage){
		//TODO 日志待整理
//		LogUtil.debug(logMessage);
//		log.info(logMessage);
	}
}
